<?php

// Include shared script

require_once('shared.php');

$stmt1 = $db->prepare(
	'SELECT			id
	FROM			sherpa_images
	WHERE			fileName = ?'
);


$stmt2 = $db->prepare(
	'SELECT			id
	FROM			sherpa_products
	WHERE			articleNumber = ?
	AND				parentId IS NULL'
);

$stmt3 = $db->prepare(
	'INSERT INTO	sherpa_images
					(languageId,
					parentProductId,
					fileName,
					created)
	VALUES			(6, ?, ?, NOW())'
);

$stmt4 = $db->prepare(
	'SELECT			imageId
	FROM			sherpa_imageToColorApplication
	WHERE			fileName = ?'
);

$stmt5 = $db->prepare(
	'SELECT			ca.id
	FROM			sherpa_products AS p
		INNER JOIN		sherpa_colorApplications AS ca
		ON				p.colorApplicationID = ca.id
		INNER JOIN		sherpa_colors AS co
		ON				ca.primaryColorId = co.id
		INNER JOIN		sherpa_colorSets AS cs
		ON				co.colorSetId = cs.id
	WHERE			p.articleNumber = ?
	AND				cs.colorSet = ?'
);

$stmt6 = $db->prepare(
	'INSERT INTO	sherpa_imageToColorApplication
					(imageId,
					colorApplicationId,
					fileName,
					created)
	VALUES			(?, ?, ?, NOW())'
);


// Import list of images
$images = array();
$imageIds = array();
$list = file('../data/Produktbilder.txt');

foreach ($list as $fileName) {
	$fileName = trim($fileName);
	$imageKey = str_replace('.jpg', '', $fileName);
	$images[$imageKey] = $fileName;
}

ksort($images);

foreach ($images as $imageKey => $fileName) {

	if (!preg_match('#(.+?)(?:-([a-z]?))*(?:-(\d+))*\.jpg#', $fileName, $matches)) {
		continue;
	}
	
	// Suject
	if (isset($matches[2]) && $matches[2] != null) {
		$sujet = $matches[1] . '-' . $matches[2];	
	}
	else {
		$sujet = $matches[1];
	}
	
	if (!isset($imageIds[$sujet])) {
		$stmt1->execute(array(
			$fileName
		));
		if ($row = $stmt1->fetch(PDO::FETCH_ASSOC)) {
			$imageId = $row['id'];
		}
		else {
			$stmt2->execute(array(
				$matches[1]
			));
			if ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
				$stmt3->execute(array(
					$row['id'],
					$fileName
				));
				
				$imageId = $db->lastInsertId();
			}
			else {
				$imageId = null;
			}
		}
		$imageIds[$sujet] = $imageId;
	}
				
	// Color application
	if (isset($matches[3])) {
		$stmt4->execute(array(
			$fileName
		));
		if (!$row = $stmt4->fetch(PDO::FETCH_ASSOC)) {
			$stmt5->execute(array(
				$matches[1],
				$matches[3]
			));
			if ($row = $stmt5->fetch(PDO::FETCH_ASSOC)) {
				$stmt6->execute(array(
					$imageIds[$sujet],
					$row['id'],
					$fileName
				));
			}
		}
	}
}

print_r($imageIds);
